﻿

'===============================================================================
' Microsoft patterns & practices Enterprise Library
' Data Access Application Block QuickStart
'===============================================================================
' Copyright © Microsoft Corporation.  All rights reserved.
' THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY
' OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT
' LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND
' FITNESS FOR A PARTICULAR PURPOSE.
'===============================================================================

Imports System.Reflection
Imports System.Globalization
Imports System.ComponentModel
Imports System.Data.SqlClient

Imports System.IO
Imports System.Collections.Generic

Imports DataAccessLayer
Imports Gurusoft.Components
Imports Microsoft.ApplicationBlocks.Data

Public Class frmList

    Private Sub frmList_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'TransferDataSet.Transcourseno' table. You can move, or remove it, as needed.
        Me.TranscoursenoTableAdapter.Fill(Me.TransferDataSet.Transcourseno)
    End Sub

    Private Function GetConnection() As SqlClient.SqlConnection
        Dim connection As New SqlConnection

        Try
            Dim sConnectionString As String = Transfer.My.MySettings.Default.TransferConnectionString.ToString()
            connection = New SqlClient.SqlConnection(sConnectionString)
            connection.Open()

        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString())
        End Try

        Return connection
    End Function

    Private Sub CheckTransferFromDB()

        If Me.IdTextBox.Text.Length = 0 Then Exit Sub

        'SqlConnection that will be used to execute the sql commands
        Dim connection As SqlClient.SqlConnection = Nothing

        Try
            connection = GetConnection()
        Catch ex As Exception
            MessageBox.Show("The connection with the database can't be established", "Application Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Sub
        End Try

        ' Set up parameters
        Dim arParms() As SqlParameter = New SqlParameter(0) {}

        ' @Id Input Parameter  
        arParms(0) = New SqlParameter("@id", SqlDbType.Int)
        arParms(0).Value = Me.IdTextBox.Text.ToString()

        Dim ds As New DataSet
        ds = SqlHelper.ExecuteDataset(connection, CommandType.StoredProcedure, "sp_CheckTransfer2", arParms)

        If ds.Tables(0).Rows.Count = 0 Then
            MessageBox.Show("ไม่พบข้อมูลใน pre_prerequisite table", "แจ้งให้ทราบ", _
                MessageBoxButtons.OK, MessageBoxIcon.Information)
            Me.txtProgress.Text = String.Empty
            Exit Sub
        Else
            ' Compare id with dbf file.            
            Dim sFoxPath As String = Transfer.My.MySettings.Default.FoxPath.ToString()
            Dim sIdFileName As String = String.Format("{0}{1}{2}", "acad", Me.IdTextBox.Text.Substring(2, 2).ToString, ".dbf")
            Dim sFullPath As String = String.Format("{0}{1}", sFoxPath, sIdFileName)

            If (MessageBox.Show("เริ่มค้นหาข้อมูลใน FoxPro " + vbNewLine + "File location " + sFullPath, "แจ้งให้ทราบ", _
                MessageBoxButtons.OKCancel, MessageBoxIcon.Information) = Windows.Forms.DialogResult.OK) Then

                Dim dtNew As New DataTable
                For Each dtx As DataRow In ds.Tables(0).Rows

                    Dim sId As String = dtx(1).ToString().Trim()
                    Dim sOldDetail As String = dtx(2).ToString().Trim()
                    Dim sCourseNo As String = dtx(3).ToString().Trim()
                    Dim sTitle As String = dtx(4).ToString().Trim()
                    Dim sCredit As String = dtx(5).ToString().Trim()
                    Dim sTerm As Integer = dtx(6).ToString().Trim()
                    Dim sYear As String = dtx(7).ToString().Trim()
                    Dim sTrm As String = dtx(8).ToString().Trim()

                    ' Get courseno and grade is not null : filter
                    Dim sCriteria As String = String.Empty

                    ' Connection DBF                
                    Dim dt As DataTable = ParseDBF.ReadDBF(sFullPath)

                    If sOldDetail.Length > 0 Then
                        ' Check criteria before and update to dbf
                        sCriteria = String.Format("{0} and {1} and {2}", sId.Trim, "(GRADE IS NOT NULL)", "GRADE NOT IN ('F','U','I','P','V','Q','W','D','D+')")

                        ' Copy data structure
                        dtNew = dt.Clone

                        For Each drNew As DataRow In dt.Select(sCriteria)
                            dtNew.ImportRow(drNew)
                        Next

                        ' Check data already : if use transfered data to dbf and sql server.
                        If dtNew.Rows.Count = 0 Then
                            ShowProgress("Uncomplete Courseno: " + sCourseNo + " Criteria: " + sOldDetail + " เพราะไม่สามารถค้นหาข้อมูลใน database FoxPro ได้ " + vbNewLine)
                        Else
                            If sOldDetail.Substring(0, 1) = "[" OrElse sOldDetail.Length = 2 Then ' 4x, 4y                                
                                ShowProgress("Uncomplete Courseno: " + sCourseNo + " Criteria: " + sOldDetail + " เพราะไม่สามารถเพิ่มข้อมูลได้ " + vbNewLine)
                            Else
                                ' Check data before insert to dbf and sql server.
                                'Dim nResult As Integer = InsertDB(Me.IdTextBox.Text, sCourseNo, sTitle, sCredit, sTrm)
                                'If nResult > 0 Then
                                ShowProgress("Completed Courseno: " + sCourseNo + " Criteria: " + sOldDetail + vbNewLine)
                                'End If
                            End If
                        End If
                    Else

                        ' Detail = NULL, insert in to dbf
                        If sOldDetail.Length = 0 Then
                            ' insert to FoxPro and SQL don't check anything.
                            ' Check data before insert to dbf and sql server.
                            'Dim nResult As Integer = InsertDB(Me.IdTextBox.Text, sCourseNo, sTitle, sCredit, sTrm)
                            'If nResult > 0 Then
                            ShowProgress("Completed Courseno: " + sCourseNo + " Criteria: " + "สามารถโอนได้เลย ถ้า Detail ใน pre_prerequisite เป็นค่าว่าง" + vbNewLine)
                            'End If
                        Else
                            sCriteria = String.Format("{0} and (CourseNo = '{1}') and {2} and {3}", sId.Trim, sCourseNo, "(GRADE IS NOT NULL)", "GRADE NOT IN ('F','U','I','P','V','Q','W','D','D+')")

                            ' Copy data structure
                            dtNew = dt.Clone

                            For Each drNew As DataRow In dt.Select(sCriteria)
                                dtNew.ImportRow(drNew)
                            Next

                            ' Check data already : if use transfered data to dbf and sql server.
                            If dtNew.Rows.Count = 0 Then
                                ShowProgress("Uncomplete Courseno: " + sCourseNo + " Criteria: " + sOldDetail + " เพราะไม่สามารถค้นหาข้อมูลใน database FoxPro ได้ " + vbNewLine)
                            Else
                                ' Check data before insert to dbf and sql server.
                                'Dim nResult As Integer = InsertDB(Me.IdTextBox.Text, sCourseNo, sTitle, sCredit, sTrm)
                                'If nResult > 0 Then
                                ShowProgress("Completed Courseno: " + sCourseNo + " Criteria: " + sOldDetail + vbNewLine)
                                'End If
                            End If
                        End If
                    End If
                Next
            End If
        End If
    End Sub

    Private Sub ShowProgress(ByVal sMessage As String)
        Me.txtProgress.Text += ">>" + sMessage
    End Sub

    Private Sub TranscoursenoBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TranscoursenoBindingNavigatorSaveItem.Click
        Me.Validate()
        Me.TranscoursenoBindingSource.EndEdit()
        Me.TranscoursenoTableAdapter.Update(Me.TransferDataSet.Transcourseno)

    End Sub
End Class